package other.database;

import org.junit.After;
import org.junit.Before;
import org.junit.Test;


import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;

public class JdbcTest {
    /**
     *  in模式存储过程
     * */
    private Connection connection;

   private SimpleDateFormat simpleDateFormat;


    private  Statement cs;

    @Before
    public void  getConnection(){
        simpleDateFormat = new SimpleDateFormat("yyyy-mm-dd:HH:mm:ss");
        connection  = DBUtil.getConnection();
    }

    @After
    public void  releaseConnection(){
        DBUtil.releaseDB(null,null,connection);
    }
    /**
     *
     * 连接已经关闭
     * */
    @Test
    public void  executeProcedure(){
        CallableStatement cs = null;
        try {
             cs =  connection.prepareCall("call barcode(?,?)");

            long startTime = System.currentTimeMillis();
            for(int i =0;i<1000;i++){
                cs.setString(1,String.valueOf(i));
                cs.setString(2,simpleDateFormat.format(new Date()));
                cs.execute();
            }
            long endTine  = System.currentTimeMillis();
            System.out.println("过程总耗时:"+(endTine-startTime));


        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                cs.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

    }

    @Test
    public void  testInAndOut(){

        try {
            CallableStatement callableStatement = connection.prepareCall("{call b2(?, ?)}");

            //callableStatement.registerOutParameter(1,Types.VARCHAR);
            callableStatement.registerOutParameter(2,Types.VARCHAR);
            callableStatement.setString(1,"1");
            callableStatement.execute();
            String value = callableStatement.getString(2);
            System.out.println("value："+value);
        } catch (SQLException e) {
            e.printStackTrace();
        }

    }
    /**
     * sql总耗时:3435
     * 连接已经关闭
     *
     * Process finished with exit code 0
     * */
    @Test
public  void insert(){
        long startTime = System.currentTimeMillis();
        String sql  = "insert into barcode(code,create_date) values(?,?)";
        PreparedStatement preparedStatement =null;
        try {
            preparedStatement  =  connection.prepareStatement(sql);
            for(int i =0;i<1000;i++){
                preparedStatement.setString(1,String.valueOf(i));
                preparedStatement.setString(2,simpleDateFormat.format(new Date()));
                preparedStatement.executeUpdate();
            }
            long endTine  = System.currentTimeMillis();
            System.out.println("sql总耗时:"+(endTine-startTime));

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            try {
                preparedStatement.close();
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    @Test
    public  void testLength(){
        System.out.println(new Date().toString().length());
    }

    @Test
    public  void insertData() throws SQLException {
        PreparedStatement preparedStatement =null;

        for(int i=0;i<1000;i++){
            char type = (char) ('a'+i);
            for(int j=1;j<=10;j++){
                String name = append(type,j);
                int l = i*10;
                int w = i*5;
                int x =1000+i*1000+j*100;
                int y =1000+i*2000+j*100;
                int z =1000+i*3000+j*100;
                int x2 = x+1000;
                int y2 = y;
                int z2 = z;
                int x3= x2-500;
                int y3 = y2-500;
                int z3 = y3 - 500;
                int x_offet=x;
                int z4 = 1000;
                String  sql = "insert into t1(type,l,w,x,y,z,x2,y2,z2,x3,y3,z3,z4,x_offset,create_time) values("+"\""+name+"\""+","+l+","+w+","+x+","+y+","+z+","+x2+","+y2+","+z2+","+x3+","+y3+","+z3+","+z4+","+x_offet+","+"now()"+")";
                System.out.println("sql:"+sql);
                preparedStatement  =  connection.prepareStatement(sql);
                preparedStatement.execute();

            }
        }
    }

    @Test
    public void testSelect() throws SQLException {
        long beginTime = System.currentTimeMillis();
        for(int i=0;i<1000;i++) {
            String sql = "select id from t1";
            PreparedStatement preparedStatement = connection.prepareStatement(sql);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
            }
        }
        long endTime = System.currentTimeMillis();
        System.out.println((endTime-beginTime)+"ms");
    }

    public String append(char c,int count){
        StringBuilder stringBuilder = new StringBuilder();
        //stringBuilder.append("$");
        for(int i=0;i<count;i++){

            stringBuilder.append(c);

        }
       //stringBuilder.append("$");
         return stringBuilder.toString();

    }
}


